﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data.SqlClient;
using SmoLiteApi.Sql;
using SmoLiteApi.Helper;

namespace SmoLiteApi
{
    /// <summary/>
    public class StoredProcedureCollection : IEnumerable<StoredProcedure>
    {
        private Database _database;

        /// <summary/>
        internal StoredProcedureCollection(Database database)
        {
            this._database = database;
        }

        /// <summary/>
        public StoredProcedure this[string name, string schemaName]
        {
            get
            {
                // get the list
                List<StoredProcedure> storedProcedures = this.FetchObjects(name, schemaName);

                // return the data
                if (storedProcedures.Count == 0)
                {
                    return null;
                }

                // return the one and only
                return storedProcedures[0];
            }
        }

        /// <summary/>
        public IEnumerator<StoredProcedure> GetEnumerator()
        {
            return this.FetchObjects(null, null).GetEnumerator();
        }

        /// <summary/>
        IEnumerator IEnumerable.GetEnumerator()
        {
            return this.FetchObjects(null, null).GetEnumerator();
        }

        /// <summary/>
        private List<StoredProcedure> FetchObjects(
            string name,
            string schemaName)
        {
            List<StoredProcedure> storedProcedures = new List<StoredProcedure>();

            StoredProcedureSelectSql storedProcedureSelectSql = new StoredProcedureSelectSql();

            // set attributes
            storedProcedureSelectSql.AddObject(new { Name = name, Schema = schemaName });

            using (SqlCommandHelper sqlCommandHelper = SqlCommandHelper.FetchSqlCommand(this._database.SqlConnection, storedProcedureSelectSql.Render()))
            {
                using (SqlDataReader sqlDataReader = sqlCommandHelper.ExecuteReader())
                {
                    while (sqlDataReader.Read())
                    {
                        StoredProcedure storedProcedure = new StoredProcedure(
                            this._database,
                            sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_NAME)),
                            sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_SCHEMA)),
                            sqlDataReader.GetString(sqlDataReader.GetOrdinal(Constant.PARAMETER_TEXT)));

                        // add to the list
                        storedProcedures.Add(storedProcedure);
                    }
                }
            }
        
            // retun the list
            return storedProcedures;
        }
    }
}
